iT邦幫忙

2021 iThome 鐵人賽
DAY 10
1
自我挑戰組

.NET Core MVC網頁應用開發系列 第 10 篇

.NET Core第10天_搭配EF Core串聯資料庫Db First_使用EntityFramework執行檢視的MVC控制器

13th鐵人賽
18947 瀏覽

  • 分享至 

  • xImage
    •  

首先新建好專案後
https://ithelp.ithome.com.tw/upload/images/20210910/201074525bkaXiaZnH.png

https://ithelp.ithome.com.tw/upload/images/20210910/20107452OPAj2i6ZBC.png

一樣記得要先弄好MVC服務注入跟MVC預設路由形式設定
(可參考.NET Core第8天_路由端點的切換_注入MVC服務_利用middleware來啟用靜態資源設置預設網址路由)

新增好空的.net core專案後
到nuget中安裝entityframework core相關套件

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
https://ithelp.ithome.com.tw/upload/images/20210910/201074527i1zZNJH39.png

https://ithelp.ithome.com.tw/upload/images/20210910/20107452pA9Cxm73fi.png

在PMC (Package Manager Console) 下Scaffold-DbContext指令
將建立既有資料庫的 EF Core 模型。
「Tools」 - 「NuGet Package Manager」 - 「Package Manager Console」，輸入以下指令：

Scaffold-DbContext "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables Employees,Orders

指令完整的格式定義

Scaffold-DbContext "Server=伺服器位置;Database=資料庫;Trusted_Connection=True;MultipleActiveResultSets=true;User ID=帳號;Password=密碼" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force

北風資料庫若本機SSMS還沒有可以自己新建
https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs

"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true"
將連接到本機SqlExpress伺服器裡頭的Northwind資料庫

Trusted_Connection=True
信任目前登入帳號登入資料庫。

MultipleActiveResultSets=true
允許在單一次DB連線中可執行多個批次作業。

-OutputDir Models
代表要將自動產生的檔案放入到 /Models之下
否則預設會統一放在專案跟目錄就容易零亂

此時若一執行會報錯
Scaffold-DbContext : 無法辨識 'Scaffold-DbContext' 詞彙是否為 Cmdlet、函數、指令檔或可執行程式的名稱。

https://ithelp.ithome.com.tw/upload/images/20210910/20107452pnmv0iMYwL.png

這裡要再補安裝一個nuget套件
Microsoft.EntityFrameworkCore.Tools
https://ithelp.ithome.com.tw/upload/images/20210910/2010745264QwgILoic.png

再次執行又遇到一個新錯誤

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SNI_PN11, error: 26 - Error Locating Server/Instance Specified)

https://ithelp.ithome.com.tw/upload/images/20210910/20107452LDendhvazN.png

這裡由於本機裝的是SQL Server 2019 打開SQLServerManager15.msc跟services.msc
做確認後原因在於現在實體指定的是指向到本機預設MSSQLSERVER而非SQLEXPRESS
因此只要打一個.即可
修正過後的指令

Scaffold-DbContext "Server=.;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables Employees,Orders

https://ithelp.ithome.com.tw/upload/images/20210910/20107452N7twNfHgWR.png

有關於SQLExpress跟MSSQLSERVER的差異可以參考這一篇來調整指令中連線字串

預設在安裝SQL Server時候若沒有去特別設定額外的實體名稱
則默認實體名稱會是.， (local)或者machine name跟ip address，當然在服務中默認捨麼都沒有改的情況下會顯示MSSQLSERVER

有特別去指定命名SQLEXPRESS的實體則寫法會是
.\SQLEXPRESS
localhost\SQLEXPRESS

當指令成功執行完後就會自動產生EF Core幫我們準備好的Model class跟Context class

https://ithelp.ithome.com.tw/upload/images/20210910/20107452Y0HKeFmTtd.png

DbContext當中可以看到
https://ithelp.ithome.com.tw/upload/images/20210910/2010745279RXaKNRa5.png

DbSet部分的變數名對應資料庫中table名稱(不可更動)
因此也會建議在table命名上建議用複數形式表示這樣子才能跟Model Class加以區隔
NorthwindContext 完整程式

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

#nullable disable

namespace prjNet5_3_DbFirst.Models
{
    public partial class NorthwindContext : DbContext
    {
        public NorthwindContext()
        {
        }

        public NorthwindContext(DbContextOptions<NorthwindContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Employee> Employees { get; set; }
        public virtual DbSet<Order> Orders { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseSqlServer("Server=.;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "Chinese_Taiwan_Stroke_CI_AS");

            modelBuilder.Entity<Employee>(entity =>
            {
                entity.HasIndex(e => e.LastName, "LastName");

                entity.HasIndex(e => e.PostalCode, "PostalCode");

                entity.Property(e => e.EmployeeId).HasColumnName("EmployeeID");

                entity.Property(e => e.Address).HasMaxLength(60);

                entity.Property(e => e.BirthDate).HasColumnType("datetime");

                entity.Property(e => e.City).HasMaxLength(15);

                entity.Property(e => e.Country).HasMaxLength(15);

                entity.Property(e => e.Extension).HasMaxLength(4);

                entity.Property(e => e.FirstName)
                    .IsRequired()
                    .HasMaxLength(10);

                entity.Property(e => e.HireDate).HasColumnType("datetime");

                entity.Property(e => e.HomePhone).HasMaxLength(24);

                entity.Property(e => e.LastName)
                    .IsRequired()
                    .HasMaxLength(20);

                entity.Property(e => e.Notes).HasColumnType("ntext");

                entity.Property(e => e.Photo).HasColumnType("image");

                entity.Property(e => e.PhotoPath).HasMaxLength(255);

                entity.Property(e => e.PostalCode).HasMaxLength(10);

                entity.Property(e => e.Region).HasMaxLength(15);

                entity.Property(e => e.Title).HasMaxLength(30);

                entity.Property(e => e.TitleOfCourtesy).HasMaxLength(25);

                entity.HasOne(d => d.ReportsToNavigation)
                    .WithMany(p => p.InverseReportsToNavigation)
                    .HasForeignKey(d => d.ReportsTo)
                    .HasConstraintName("FK_Employees_Employees");
            });

            modelBuilder.Entity<Order>(entity =>
            {
                entity.HasIndex(e => e.CustomerId, "CustomerID");

                entity.HasIndex(e => e.CustomerId, "CustomersOrders");

                entity.HasIndex(e => e.EmployeeId, "EmployeeID");

                entity.HasIndex(e => e.EmployeeId, "EmployeesOrders");

                entity.HasIndex(e => e.OrderDate, "OrderDate");

                entity.HasIndex(e => e.ShipPostalCode, "ShipPostalCode");

                entity.HasIndex(e => e.ShippedDate, "ShippedDate");

                entity.HasIndex(e => e.ShipVia, "ShippersOrders");

                entity.Property(e => e.OrderId).HasColumnName("OrderID");

                entity.Property(e => e.CustomerId)
                    .HasMaxLength(5)
                    .HasColumnName("CustomerID")
                    .IsFixedLength(true);

                entity.Property(e => e.EmployeeId).HasColumnName("EmployeeID");

                entity.Property(e => e.Freight)
                    .HasColumnType("money")
                    .HasDefaultValueSql("((0))");

                entity.Property(e => e.OrderDate).HasColumnType("datetime");

                entity.Property(e => e.RequiredDate).HasColumnType("datetime");

                entity.Property(e => e.ShipAddress).HasMaxLength(60);

                entity.Property(e => e.ShipCity).HasMaxLength(15);

                entity.Property(e => e.ShipCountry).HasMaxLength(15);

                entity.Property(e => e.ShipName).HasMaxLength(40);

                entity.Property(e => e.ShipPostalCode).HasMaxLength(10);

                entity.Property(e => e.ShipRegion).HasMaxLength(15);

                entity.Property(e => e.ShippedDate).HasColumnType("datetime");

                entity.HasOne(d => d.Employee)
                    .WithMany(p => p.Orders)
                    .HasForeignKey(d => d.EmployeeId)
                    .HasConstraintName("FK_Orders_Employees");
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

在繼承自DbContext的NorthwindContext.cs程式中預設在
OnConfiguring方法中有一串標記為警告的代表含意是說不建議把連線資訊放在程式中
(可能不安全或者不易更換建議寫於設定檔)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.

那可以先擺著

在此我們可看到
Employee類別對應資料庫表當中型態產生的Model
https://ithelp.ithome.com.tw/upload/images/20210910/201074523BuWZqYXLS.png

Employee.cs

using System;
using System.Collections.Generic;

#nullable disable

namespace prjNet5_3_DbFirst.Models
{
    public partial class Employee
    {
        public Employee()
        {
            InverseReportsToNavigation = new HashSet<Employee>();
            Orders = new HashSet<Order>();
        }

        public int EmployeeId { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Title { get; set; }
        public string TitleOfCourtesy { get; set; }
        public DateTime? BirthDate { get; set; }
        public DateTime? HireDate { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string Region { get; set; }
        public string PostalCode { get; set; }
        public string Country { get; set; }
        public string HomePhone { get; set; }
        public string Extension { get; set; }
        public byte[] Photo { get; set; }
        public string Notes { get; set; }
        public int? ReportsTo { get; set; }
        public string PhotoPath { get; set; }

        public virtual Employee ReportsToNavigation { get; set; }
        public virtual ICollection<Employee> InverseReportsToNavigation { get; set; }
        public virtual ICollection<Order> Orders { get; set; }
    }
}

使用精靈產生檢視畫面與控制器

https://ithelp.ithome.com.tw/upload/images/20210910/20107452hb2ytDh1Yh.png

https://ithelp.ithome.com.tw/upload/images/20210910/20107452rDhyFf6mPJ.png

模型類別:先選Employee
內容類別:就是選NorthwindContext
暫時勾消版面配置

https://ithelp.ithome.com.tw/upload/images/20210910/20107452msqiIV1hbU.png

預設就會把EmployeeController跟View對應操作畫面檢視產生出來
https://ithelp.ithome.com.tw/upload/images/20210910/20107452glOE5ce5JX.png

EmployeesController.cs的程式碼

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using prjNet5_3_DbFirst.Models;

namespace prjNet5_3_DbFirst.Controllers
{
    public class EmployeesController : Controller
    {
        private readonly NorthwindContext _context;

        public EmployeesController(NorthwindContext context)
        {
            _context = context;
        }

        // GET: Employees
        public async Task<IActionResult> Index()
        {
            var northwindContext = _context.Employees.Include(e => e.ReportsToNavigation);
            return View(await northwindContext.ToListAsync());
        }

        // GET: Employees/Details/5
        public async Task<IActionResult> Details(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var employee = await _context.Employees
                .Include(e => e.ReportsToNavigation)
                .FirstOrDefaultAsync(m => m.EmployeeId == id);
            if (employee == null)
            {
                return NotFound();
            }

            return View(employee);
        }

        // GET: Employees/Create
        public IActionResult Create()
        {
            ViewData["ReportsTo"] = new SelectList(_context.Employees, "EmployeeId", "FirstName");
            return View();
        }

        // POST: Employees/Create
        // To protect from overposting attacks, enable the specific properties you want to bind to.
        // For more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("EmployeeId,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,Address,City,Region,PostalCode,Country,HomePhone,Extension,Photo,Notes,ReportsTo,PhotoPath")] Employee employee)
        {
            if (ModelState.IsValid)
            {
                _context.Add(employee);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            ViewData["ReportsTo"] = new SelectList(_context.Employees, "EmployeeId", "FirstName", employee.ReportsTo);
            return View(employee);
        }

        // GET: Employees/Edit/5
        public async Task<IActionResult> Edit(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var employee = await _context.Employees.FindAsync(id);
            if (employee == null)
            {
                return NotFound();
            }
            ViewData["ReportsTo"] = new SelectList(_context.Employees, "EmployeeId", "FirstName", employee.ReportsTo);
            return View(employee);
        }

        // POST: Employees/Edit/5
        // To protect from overposting attacks, enable the specific properties you want to bind to.
        // For more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Edit(int id, [Bind("EmployeeId,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,Address,City,Region,PostalCode,Country,HomePhone,Extension,Photo,Notes,ReportsTo,PhotoPath")] Employee employee)
        {
            if (id != employee.EmployeeId)
            {
                return NotFound();
            }

            if (ModelState.IsValid)
            {
                try
                {
                    _context.Update(employee);
                    await _context.SaveChangesAsync();
                }
                catch (DbUpdateConcurrencyException)
                {
                    if (!EmployeeExists(employee.EmployeeId))
                    {
                        return NotFound();
                    }
                    else
                    {
                        throw;
                    }
                }
                return RedirectToAction(nameof(Index));
            }
            ViewData["ReportsTo"] = new SelectList(_context.Employees, "EmployeeId", "FirstName", employee.ReportsTo);
            return View(employee);
        }

        // GET: Employees/Delete/5
        public async Task<IActionResult> Delete(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var employee = await _context.Employees
                .Include(e => e.ReportsToNavigation)
                .FirstOrDefaultAsync(m => m.EmployeeId == id);
            if (employee == null)
            {
                return NotFound();
            }

            return View(employee);
        }

        // POST: Employees/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> DeleteConfirmed(int id)
        {
            var employee = await _context.Employees.FindAsync(id);
            _context.Employees.Remove(employee);
            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }

        private bool EmployeeExists(int id)
        {
            return _context.Employees.Any(e => e.EmployeeId == id);
        }
    }
}

這裡預設直接用精靈產生的仍然會執行報錯!

https://ithelp.ithome.com.tw/upload/images/20210910/20107452cxGSUy8vPy.png

主要原因就是因為warning錯誤標記那段我們直接忽略沒調整
但事實上那段資料庫連線並沒有起到作用所以直接訪問會報錯

這裡我們把NorthwindContext.cs當中的
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)這方法裡面的連線字串程式碼整段註解或刪除掉

將連線字串("Server=.;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true")

若你是屬於有實體名稱的反斜線記得要兩條
("Server=.\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true")

設定移置appsettings.json
預設appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

調整後的appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DbConnectonString": "Server=伺服器位置;Database=資料庫;Trusted_Connection=True;MultipleActiveResultSets=true;User ID=帳號;Password=密碼"
  }
}

所以在程式中KEY值取得就是透過DbConnectonString
在Startup.cs中加入資料庫物件的DI (服務注入一律寫在ConfigureServices)
加入如下程式調整
https://ithelp.ithome.com.tw/upload/images/20210910/20107452wt0lcSRDF5.png

調整過後的Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using prjNet5_3_DbFirst.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace prjNet5_3_DbFirst
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddDbContext<NorthwindContext>(options =>options.UseSqlServer(Configuration.GetConnectionString("DbConnectonString")));
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllerRoute(
                    name: "default",
                    pattern: "{Controller=Home}/{Action=Index}/{id?}"
                );//設置MVC默認路由
            });
        }
    }
}

再次執行就正常了
https://ithelp.ithome.com.tw/upload/images/20210910/20107452XPtHjzF9ae.png

本文同步發表至個人部落格
https://coolmandiary.blogspot.com/2021/07/net-core10ef-coredb-first.html

Ref:
教學課程：開始使用 ASP.NET MVC web 應用程式中的 EF Core
https://docs.microsoft.com/zh-tw/aspnet/core/data/ef-mvc/intro?view=aspnetcore-5.0

還原工程 (Scaffolding)
https://docs.microsoft.com/zh-tw/ef/core/managing-schemas/scaffolding?tabs=dotnet-core-cli

ASP.NET Core - (DB-First) Scaffold-DbContext的錯誤訊息
https://dotblogs.com.tw/mis2000lab/2020/02/05/ASPnet_Core_Scaffold-DbContext_Error

對 SQL Server 資料庫引擎的連線進行疑難排解
https://docs.microsoft.com/zh-tw/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15

[MSSQL] 無法連線到 MS-SQL error-a-network-related-or-instance-specific-error-occurred-while-establishing
https://marcus116.blogspot.com/2017/12/error-a-network-related-or-instance-specific-error-occurred-while-establishing.html

What is the difference between SQLEXPRESS and MSSQLSERVER?
https://dba.stackexchange.com/questions/22246/what-is-the-difference-between-sqlexpress-and-mssqlserver/22248


上一篇
.NET Core第9天_MVC_Model的引入
下一篇
.NET Core第11天_Controller定義_附加屬性_資料接收方式_返回View方式
系列文
.NET Core MVC網頁應用開發 共 30 篇
  1. 26
    .NET Core第26天_ScriptTagHelper的使用
  2. 27
    .NET Core第27天_CacheTagHelper的使用
  3. 28
    .NET Core第28天_ValidationMessageTagHelper和ValidationSummaryTagHelper的使用
  4. 29
    .NET Core第29天_Model驗證配置準備流程_各種驗證資料註解使用方式
  5. 30
    (完結).NET Core第30天_Controller Action的各種不同回傳
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22203 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙